/**
 *
 */
package vn.tecviets.thuandt.model.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.sun.org.apache.regexp.internal.recompile;

import vn.tecviets.thuandt.model.bean.Post;
import vn.tecviets.thuandt.model.common.ConnectionProvider;

/*
 * PostDAO
 * 1.0
 * Mar 19, 2013
 * 
 * Copyright notice
 * 
 * Modification Logs:
 * 
 * DATE					AUTHOR				DESCRIPTION
 * -----------------------------------------------------------
 * Mar 19, 2013			Thuandt				Create
 */

public class PostDAO {

	private Connection con = null;
	private CallableStatement cs = null;
	private ResultSet rs = null;
	private String sql = null;

	/**
	 * Get total of posts
	 * 
	 * @return total of posts
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int countTotalPost() throws ClassNotFoundException, SQLException {
		sql = "{call sp_CountTotalPost}";
		int totalPosts = 0;

		try {

			con = ConnectionProvider.getConnection();
			cs = con.prepareCall(sql);
			rs = cs.executeQuery();

			while (rs.next()) {
				totalPosts = rs.getInt("TotalPost");
			}

		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (cs != null) {
				cs.close();
			}

			if (con != null) {
				con.close();
			}
		}

		return totalPosts;
	}

	/**
	 * Get Title, IsShowed, IsImportant, CreatedDate, ModifiedDate,
	 * CategoryTitle, Username, CategoryId
	 * 
	 * @param from
	 * @param to
	 * @return a post
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public ArrayList<Post> selectPostForPaging(int from, int to) throws ClassNotFoundException, SQLException {
		sql = "{call sp_SelectPostForPaging(?, ?)}";
		ArrayList<Post> postArrayList = new ArrayList<Post>();

		try {
			con = ConnectionProvider.getConnection();
			cs = con.prepareCall(sql);
			cs.setInt(1, from);
			cs.setInt(2, to);
			rs = cs.executeQuery();

			while (rs.next()) {
				Post post = new Post();
				post.setTitle(rs.getNString("Title"));
				post.setShowed(Boolean.toString(rs.getBoolean("IsShowed")));
				post.setImportant(Boolean.toString(rs.getBoolean("IsImportant")));
				post.setCreatedDate(rs.getDate("CreatedDate"));
				post.setModifiedDate(rs.getDate("ModifiedDate"));
				post.setCategoryTitle(rs.getNString("CategoryTitle"));
				post.setUsername(rs.getNString("Username"));
				post.setPostId(rs.getInt("PostId"));
				post.setCategoryId(rs.getInt("CategoryId"));
				postArrayList.add(post);
			}
		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (cs != null) {
				cs.close();
			}

			if (con != null) {
				con.close();
			}
		}

		return postArrayList;
	}

	/**
	 * Delete a specific post
	 * 
	 * @param postId
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public String deletePost(int postId) throws ClassNotFoundException, SQLException {

		sql = "{call sp_DeletePost(?)}";
		String pathname = null;
		
		try {

			con = ConnectionProvider.getConnection();
			cs = con.prepareCall(sql);
			cs.setInt(1, postId);
			rs = cs.executeQuery();
			
			while (rs.next()) {
				pathname = rs.getNString("Pathname");
			}

		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (cs != null) {
				cs.close();
			}

			if (con != null) {
				con.close();
			}
		}
		
		return pathname;
	}

	/**
	 * Insert Title, CategoryId, Image, Caption, Summary, IsShowed, IsImportant,
	 * Pathname, CreatedDate, ModifiedDate, Username of a new post
	 * 
	 * @param post
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public void insertPost(Post post) throws ClassNotFoundException, SQLException {
		sql = "{call sp_InsertPost(?, ?, ?, ?, ?, ?, ?, ?, ?)}";

		try {

			con = ConnectionProvider.getConnection();
			cs = con.prepareCall(sql);
			cs.setNString(1, post.getTitle());
			cs.setInt(2, post.getCategoryId());
			cs.setNString(3, post.getImage());
			cs.setNString(4, post.getCaption());
			cs.setNString(5, post.getSummary());
			cs.setBoolean(6, Boolean.parseBoolean(post.getShowed()));
			cs.setBoolean(7, Boolean.parseBoolean(post.getImportant()));
			cs.setNString(8, post.getPathname());
			cs.setNString(9, post.getUsername());
			cs.executeUpdate();

		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (cs != null) {
				cs.close();
			}

			if (con != null) {
				con.close();
			}
		}
	}

	/**
	 * Get Title, CategoryTitle, Image, Caption, Summary, IsShowed, IsImportant,
	 * Pathname, CategoryId
	 * 
	 * @param postId
	 * @return a post 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Post selectPostByID(int postId) throws ClassNotFoundException, SQLException {
		sql = "{call sp_SelectPostByPostId(?)}";
		Post post = new Post();

		try {

			con = ConnectionProvider.getConnection();
			cs = con.prepareCall(sql);
			cs.setInt(1, postId);
			rs = cs.executeQuery();

			while (rs.next()) {
				post.setTitle(rs.getNString("Title"));
				post.setCategoryTitle(rs.getNString("CategoryTitle"));
				post.setImage(rs.getNString("Image"));
				post.setCaption(rs.getNString("Caption"));
				post.setSummary(rs.getNString("Summary"));
				post.setShowed(Boolean.toString(rs.getBoolean("IsShowed")));
				post.setImportant(Boolean.toString(rs.getBoolean("IsImportant")));
				post.setPathname(rs.getNString("Pathname"));
				post.setCategoryId(rs.getInt("CategoryId"));
			}

		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		}

		return post;
	}

	/**
	 * @param post
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public void updatePost(Post post) throws ClassNotFoundException, SQLException {
		sql = "{call sp_UpdatePost(?, ?, ?, ?, ?, ?, ?, ?)}";

		try {

			con = ConnectionProvider.getConnection();
			cs = con.prepareCall(sql);
			cs.setInt(1, post.getPostId());
			cs.setNString(2, post.getTitle());
			cs.setInt(3, post.getCategoryId());
			cs.setNString(4, post.getImage());
			cs.setNString(5, post.getCaption());
			cs.setNString(6, post.getSummary());
			cs.setBoolean(7, Boolean.parseBoolean(post.getShowed()));
			cs.setBoolean(8, Boolean.parseBoolean(post.getImportant()));
			cs.executeUpdate();

		} catch (ClassNotFoundException e) {
			throw e;
		} catch (SQLException e) {
			throw e;
		} finally {
			if (cs != null) {
				cs.close();
			}

			if (con != null) {
				con.close();
			}
		}
	}

}
